﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DMS.DAL;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Data.EntityClient;

namespace DMS.DAL
{
    public class DALclass
    {
        #region global
        private DMSEntities dms = null;
        //private static DALclass _instance = null;
        public static DALclass getInstance()
        {
			DALclass instance = HttpContext.Current.Items["DALclass"] as DALclass;
			if (instance == null)
			{
				instance = new DALclass();
				HttpContext.Current.Items.Add("DALclass", instance);
			}

            return instance;
        }
        private DALclass()
        {
            dms = new DMSEntities();
        }
        #endregion
        //put you code here
        #region DocumentLibrary
        public int saveDocumentLibrary(DocumentLibrary dl)
        {
            if (dl.ID == 0)
                dms.AddToDocumentLibrary(dl);
            return dms.SaveChanges();
        }
        public DocumentLibrary getDocumentLibraryById(int documentLibraryId)
        {
            var query = from d in dms.DocumentLibrary
                        where d.ID == documentLibraryId
                        select d;
            if (query.Count() > 0)
                return query.First();
            else
                return null;
        }
        public List<DocumentLibrary> getAllDocumentLibrary()
        {
            var query = from d in dms.DocumentLibrary
                        orderby d.ID ascending
                        select d;
            if (query.Count() > 0)
                return query.ToList();
            else
                return null;
        }
        #endregion
        #region folders
        public List<Folder> getFoldersByLibrary(int libraryId, int current)
        {
            var query = from d in dms.Folder
                        where d.DocumentLibrary.ID == libraryId
                        && d.ID != current
                        && d.Folder2 ==null
                        select d;
            if (query.Count() > 0)
                return query.ToList();
            else
                return null;
        }
        public List<Folder> getFoldersByLibraryAndFolder(int libraryId, int folderId, int current)
        {
            var query = from d in dms.Folder
                        where d.DocumentLibrary.ID == libraryId
                        && d.ID != current
                        &&
                        (d.Folder2 != null
                        &&
                        d.Folder2.ID == folderId)
                        orderby d.Name
                        select d;
            if (query.Count() > 0)
                return query.ToList();
            else
                return null;
        }
 
        public Folder getFolderById(int folderId)
        {
            var query = from d in dms.Folder
                        where d.ID == folderId
                        select d;
            if (query.Count() > 0)
                return query.First();
            else
                return null;
        }
        public int saveFolder(Folder fldr)
        {
            if (fldr.ID == 0)
                dms.AddToFolder(fldr);
            return dms.SaveChanges();
        }
        #endregion
        #region User
        public User getUserById(int id)
        {
            var query = from d in dms.User
                        where d.ID == id
                        select d;
            if (query.Count() > 0)
                return query.First();
            else
                return null;
        }
        public List<User> getAllUser()
        {
            var query = from d in dms.User
                        select d;
            if (query.Count() > 0)
                return query.ToList();
            else
                return null;
        }
        #endregion
        #region storage provider
        public StorageProvider getStorageProviderById(int id)
        {
            var query = from d in dms.StorageProvider
                        where d.ID == id
                        select d;
            if (query.Count() > 0)
                return query.First();
            else
                return null;
        }
        public List<StorageProvider> getAllStorageProvider()
        {
            var query = from d in dms.StorageProvider
                        orderby d.ID
                        select d;
            if (query.Count() > 0)
                return query.ToList();
            else
                return null;
        }
        #endregion
        #region Misc
        public void deleteObject(object obj)
        {
            dms.DeleteObject(obj);
            dms.SaveChanges();
        }
        #endregion
        #region userpermission
        public int saveUserPermission(UserPermission up)
        {
            if (!isUserPermissionExists(up))
                dms.AddToUserPermission(up);
            return dms.SaveChanges();
        }
        bool isUserPermissionExists(UserPermission up)
        {
            var query = from d in dms.UserPermission
                        where d.DocumentLibraryID == up.DocumentLibraryID
                        && d.UserID == up.UserID
                        select d;
            return (query.Count() == 1);
        }
        public UserPermission getUserPermission(int userId, int documentLibId)
        {
            var query = from d in dms.UserPermission
                        where d.DocumentLibraryID == documentLibId
                        && d.UserID == userId
                        select d;
            if (query.Count() > 0)
                return query.First();
            else
                return null;
        }
        public List<UserPermission> getUserPermissionForDocumentLibrary(int documentLibraryId)
        {
            var query = from d in dms.UserPermission
                        where d.DocumentLibraryID == documentLibraryId
                        select d;
            if (query.Count() > 0)
                return query.ToList();
            else
                return null;
        }
        #endregion
        public int AddDocument(Document document)
		{
			dms.AddToDocument(document);
			
			dms.SaveChanges();
			dms.AcceptAllChanges();
			return document.ID;
			
		}

		public int SaveVersion(DocumentVersion version)
		{
			dms.AddToDocumentVersion(version);
			dms.SaveChanges();

			return version.ID;
		}

		public void StoreFile(int versionId, byte[] data)
		{
			DbConnection con = (dms.Connection as EntityConnection).StoreConnection;//new SqlConnection(ConfigurationManager.ConnectionStrings["dms"].ToString());
			var cmd= con.CreateCommand();
			cmd.CommandText = "DocumentVersionUpdateFile";
			cmd.CommandType = System.Data.CommandType.StoredProcedure;
			var paramId = cmd.CreateParameter();
			paramId.Direction = System.Data.ParameterDirection.Input;
			paramId.ParameterName = "@VersionId";
			paramId.Value = versionId;
			cmd.Parameters.Add(paramId);

			var paramData = cmd.CreateParameter();
			paramData.Direction = System.Data.ParameterDirection.Input;
			paramData.ParameterName = "@data";
			paramData.DbType = System.Data.DbType.Binary;
			paramData.Size = data.Length;
			paramData.Value = data;
			cmd.Parameters.Add(paramData);

			con.Open();
			cmd.ExecuteNonQuery();
			con.Close();

		}

		public Document GetDocumentById(int documentId)
		{
			var doc = dms.Document.Where(d => d.ID == documentId).FirstOrDefault();
			dms.Detach(doc);
			return doc;

		}

		public byte[] GetDocumentData(int documentId)
		{
			//get latest version
			var doc = dms.Document.Where(d => d.ID == documentId).FirstOrDefault();
			doc.DocumentVersion.Load();
			var version = doc.DocumentVersion.OrderBy(v => v.ID).FirstOrDefault();

			DbConnection con = (dms.Connection as EntityConnection).StoreConnection;//new SqlConnection(ConfigurationManager.ConnectionStrings["dms"].ToString());
			var cmd = con.CreateCommand();
			cmd.CommandText = "select data from DocumentVersion where ID=@version";

			var p = cmd.CreateParameter();
			p.ParameterName = "@version";
			p.Value = version.ID;
			cmd.Parameters.Add(p);

			con.Open();
			byte[] data = (byte[])cmd.ExecuteScalar();
			con.Close();

			return data;




			
			
		}
	}
}
